unit Class_Stud;

interface
uses
  Classes,SysUtils,ADODB;

type
  TStud = class (TObject)
  private
    FStud_ID : Integer;
    FStud_Code : string;
    FStud_Name : string;
    FStud_Call : string;
    FBank : String;
    Faccounts : string;
    
  published
    property Stud_ID : Integer read FStud_ID write FStud_ID;
    property Stud_Code : string read FStud_Code write FStud_Code;
    property Stud_Name : string read FStud_Name write FStud_Name;
    property Stud_Call : string read FStud_Call write FStud_Call;
    property Bank : string read FBank write FBank;
    property accounts : string read Faccounts write Faccounts;
  public
    procedure Insert(aADOConnetion:TADOConnection);
    procedure update(aADOConnetion:TADOConnection);
    procedure delete(aADOConnetion:TADOConnection);
    function SelectStudIDByStudCode(aADOConnetion:TADOConnection):Integer;
    function  GetNextStudID(aADOConnetion:TADOConnection):Integer;
    class procedure select(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
  public
    destructor Destroy; override;
    constructor Create;
  end;

implementation

{ TStud }

constructor TStud.Create;
begin

end;

destructor TStud.Destroy;
begin
  inherited;
end;

procedure TStud.Insert(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

    with cADOC do
    begin
      cSQL := 'insert into tbl_stud (accounts,Bank,Stud_id,stud_code,stud_name,stud_call) values (:accounts,:Bank,:Stud_id,:stud_code,:stud_name,:stud_call)';
      CommandText := cSQL;
      Parameters.ParamByName('stud_id').Value := self.Stud_id;
      Parameters.ParamByName('stud_code').Value := self.Stud_Code;
      Parameters.ParamByName('stud_name').Value := self.stud_name;
      Parameters.ParamByName('stud_call').Value := self.stud_call;
      Parameters.ParamByName('accounts').Value := self.accounts;
      Parameters.ParamByName('Bank').Value := self.Bank;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;
procedure TStud.delete(aADOConnetion: TADOConnection);

var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;
    with cADOC do
    begin
      cSQL := 'DELETE tbl_stud where stud_code=:stud_code ';
      CommandText := cSQL;

      Parameters.ParamByName('stud_code').Value := self.stud_code;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

procedure TStud.update(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

  with cADOC do
  begin
    cSQL := 'update tbl_stud set stud_name=:stud_name,Stud_Call=:Stud_Call,accounts=:accounts,Bank=:Bank where stud_code=:stud_code';
    CommandText := cSQL;

    Parameters.ParamByName('stud_code').Value := self.stud_code;
    Parameters.ParamByName('stud_name').Value := self.Stud_Name;
    Parameters.ParamByName('stud_call').Value := self.Stud_Call;
    Parameters.ParamByName('accounts').Value := self.accounts;
    Parameters.ParamByName('Bank').Value := self.Bank;
  end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

class procedure TStud.select(aADOConnetion: TADOConnection;
  var aList: TStringList;aSQL:string);
var
  cStud:TStud;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_stud ';
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      cStud := TStud.Create;
      aList.AddObject('',cStud);

      cStud.Stud_ID := ADOD.FieldByName('STUD_ID').AsInteger;
      cStud.Stud_Code := ADOD.FieldByName('STUD_CODE').Asstring;
      cStud.Stud_Name := ADOD.FieldByName('STUD_NAME').AsString;
      cStud.Stud_call := ADOD.FieldByName('STUD_CALL').AsString;
      cStud.Bank := ADOD.FieldByName('bank').AsString;
      cStud.accounts := ADOD.FieldByName('accounts').AsString;

      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;
  
end;




function TStud.GetNextStudID(aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select max(stud_id)+1 as max_stud_id from tbl_stud';
    cADOD.Open;

    Result := cADOD.FieldByName('max_stud_id').AsInteger;

    if Result = 0 then Result := 1;
  finally
    FreeAndNil(cADOD);
  end;
end;

function TStud.SelectStudIDByStudCode(aADOConnetion:TADOConnection):Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select Stud_id from tbl_Stud where Stud_Code=:Stud_Code';
    with cADOD.Parameters do
    begin
      ParamByName('Stud_Code').Value := self.Stud_Code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('aStud_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

end.
